﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Environment;
using Portal.Language;
using Portal.Interface;

namespace Portal.CMS
{
    public class VoteBLL
    {
        public VoteCls CreateModel(string ActiveLanguage, string VoteId)
        {
            return CreateModel(Environments.ConnectionString, ActiveLanguage, VoteId);
        }

        public VoteCls CreateModel(string ConnectionString, string ActiveLanguage, string VoteId)
        {
            string Query = "select * from TableVote where VoteId=@VoteId";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                VoteCls VoteCls = new VoteCls();
                VoteCls.VoteId = (string)dtResult.Rows[0]["VoteId"];
                VoteCls.Subject = "";
                try
                {
                    VoteCls.Subject = (string)dtResult.Rows[0][ActiveLanguage + "Subject"];
                }
                catch { }
                try
                {
                    VoteCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }
                try
                {
                    VoteCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                }
                catch { }
                return VoteCls;
            }
        }

        public DataTable ReadingForSyncFromTool(string ConnectionString, string belongSite)
        {
            string Query = "select VoteId from TableVote";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            //sqlCommand.Parameters.Add("belongSite", SqlDbType.NVarChar).Value = belongSite;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable Reading(string belongSite)
        {
            string Query = "select * from TableVote where belongSite=@belongSite";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("belongSite", SqlDbType.NVarChar).Value = belongSite;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingVoteItems(string VoteId)
        {
            string Query = "select * from TableVoteItem where fk_Vote_Id='" + VoteId + "' order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string AddVote(SqlTransaction sqlTransaction, string belongSite, string ActiveLanguage, string Subject, VoteItemCls[] VoteItems)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string VoteId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableVote(VoteId,[" + ActiveLanguage + "Subject],belongSite) values (@VoteId,@Subject,@belongSite)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            try
            {
                sqlCommand.ExecuteNonQuery();
                //doan nay call webservice
                // DataSet ds = new DataSet("xml");

                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    ds.Tables.Add("Votes");
                //    ds.Tables.Add("VotesItems");

                //    ds.Tables[0].Columns.Add("ActiveLanguage");
                //    ds.Tables[0].Columns.Add("VoteId");
                //    ds.Tables[0].Columns.Add("Subject");
                //    ds.Tables[0].Columns.Add("Owner");

                //    ds.Tables[1].Columns.Add("VoteItemId");
                //    ds.Tables[1].Columns.Add("VoteItemName");
                //    ds.Tables[1].Columns.Add("IndexOrder");

                //    string owner = Environment.Environments.CurrentLoginUser.LoginName;
                //    ds.Tables[0].Rows.Add(new object[]{
                //        ActiveLanguage,VoteId,Subject,owner
                //    });
                //}
                for (int iIndex = 0; iIndex < VoteItems.Length; iIndex++)
                {
                    if (VoteItems[iIndex].VoteItemName != null)
                    {
                        if (VoteItems[iIndex].VoteItemName.Trim().Length > 0)
                        {
                            Query = "Insert into TableVoteItem(VoteItemId,VoteItemName,[" + ActiveLanguage + "VoteItemName],TotalVote,IndexOrder,fk_Vote_Id) values (@VoteItemId,@VoteItemName,@VoteItemName, 0,@IndexOrder,@fk_Vote_Id)";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();
                            string VoteItemId = System.Guid.NewGuid().ToString();
                            sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@VoteItemId"].Value = VoteItemId;

                            sqlCommand.Parameters.Add("@VoteItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@VoteItemName"].Value = VoteItems[iIndex].VoteItemName;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = VoteItems[iIndex].IndexOrder;

                            sqlCommand.Parameters.Add("@fk_Vote_Id", SqlDbType.VarChar);
                            sqlCommand.Parameters["@fk_Vote_Id"].Value = VoteId;

                            sqlCommand.ExecuteNonQuery();

                            //if (Portal.Environment.Environments.UseFrontEndDatabase)
                            //{
                            //    ds.Tables[1].Rows.Add(new object[]{
                            //           VoteItemId,VoteItems[iIndex].VoteItemName,VoteItems[iIndex].IndexOrder.ToString()
                            //       });
                            //}
                        }
                    }
                }
                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    string XmlData = ds.GetXml();
                //    Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //    for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //    {
                //        string RetValue = WServices[iIndexService].AddVotes(Environments.SecurityCode, XmlData);
                //        if (RetValue.Trim().Length > 0)
                //        {
                //            throw new Exception("Webservice:" + RetValue);
                //        }
                //    }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return VoteId;
        }

        public void AppendVote(
            SqlTransaction sqlTransaction,
            string ActiveLanguage,
            string VoteId,
            string VoteName,
            VoteItemCls[] VoteItems)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Transaction = sqlTransaction;
            string Query = "Update TableVote set [" + ActiveLanguage + "Subject]=@Subject where VoteId=@VoteId";
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = VoteName;

            try
            {
                sqlCommand.ExecuteNonQuery();
                //doan nay call webservice
                DataSet ds = new DataSet("xml");

                if (Portal.Environment.Environments.UseFrontEndDatabase)
                {
                    ds.Tables.Add("Vote");
                    ds.Tables.Add("VoteItems");

                    ds.Tables[0].Columns.Add("ActiveLanguage");
                    ds.Tables[0].Columns.Add("VoteId");
                    ds.Tables[0].Columns.Add("Subject");

                    ds.Tables[1].Columns.Add("VoteItemId");
                    ds.Tables[1].Columns.Add("VoteItemName");
                    ds.Tables[1].Columns.Add("IndexOrder");


                    ds.Tables[0].Rows.Add(new object[]{
                        ActiveLanguage,VoteId,VoteName
                    });
                }
                for (int iIndex = 0; iIndex < VoteItems.Length; iIndex++)
                {
                    if (VoteItems[iIndex].VoteItemName != null)
                    {
                        if (VoteItems[iIndex].VoteItemName.Trim().Length > 0)
                        {
                            Query = "Insert into TableVoteItem(VoteItemId," + ActiveLanguage + "VoteItemName],TotalVote,IndexOrder,fk_Vote_Id) values (@VoteItemId,@VoteItemName,0,@IndexOrder,@fk_Vote_Id)";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();
                            sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@VoteItemId"].Value = System.Guid.NewGuid().ToString();

                            sqlCommand.Parameters.Add("@VoteItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@VoteItemName"].Value = VoteItems[iIndex].VoteItemName;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = VoteItems[iIndex].IndexOrder;

                            sqlCommand.Parameters.Add("@fk_Vote_Id", SqlDbType.VarChar);
                            sqlCommand.Parameters["@fk_Vote_Id"].Value = VoteId;

                            sqlCommand.ExecuteNonQuery();


                            if (Portal.Environment.Environments.UseFrontEndDatabase)
                            {
                                ds.Tables[1].Rows.Add(new object[]{
                                       VoteItems[iIndex].VoteItemId,VoteItems[iIndex].VoteItemName,VoteItems[iIndex].IndexOrder.ToString()
                                   });
                            }
                        }
                    }
                }

                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    string XmlData = ds.GetXml();
                //    Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //    for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //    {
                //        string RetValue = WServices[iIndexService].AppendVote(Environments.SecurityCode, XmlData);
                //        if (RetValue.Trim().Length > 0)
                //        {
                //            throw new Exception("Webservice:" + RetValue);
                //        }
                //    }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void UpdateVote(SqlTransaction sqlTransaction, string ActiveLanguage, string VoteId, string Subject, VoteItemCls[] VoteItems)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableVote set [" + ActiveLanguage + "Subject]=@Subject where VoteId=@VoteId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            try
            {
                sqlCommand.ExecuteNonQuery();

                //doan nay call webservice
                DataSet ds = new DataSet("xml");

                if (Portal.Environment.Environments.UseFrontEndDatabase)
                {
                    ds.Tables.Add("Votes");
                    ds.Tables.Add("VotesItems");

                    ds.Tables[0].Columns.Add("ActiveLanguage");
                    ds.Tables[0].Columns.Add("VoteId");
                    ds.Tables[0].Columns.Add("Subject");


                    ds.Tables[1].Columns.Add("VoteItemId");
                    ds.Tables[1].Columns.Add("VoteItemName");
                    ds.Tables[1].Columns.Add("IndexOrder");


                    ds.Tables[0].Rows.Add(new object[]{
                        ActiveLanguage,VoteId,Subject
                    });
                }


                sqlCommand.Parameters.Clear();


                string VoteIds = "(";
                for (int iIndex = 0; iIndex < VoteItems.Length; iIndex++)
                {
                    if (VoteItems[iIndex].VoteItemId == null) VoteItems[iIndex].VoteItemId = System.Guid.NewGuid().ToString();
                    VoteIds = VoteIds + "'" + VoteItems[iIndex].VoteItemId + "'";
                    if (iIndex < VoteItems.Length - 1)
                    {
                        VoteIds += ",";
                    }

                    if (VoteItems[iIndex].VoteItemName != null)
                    {
                        if (VoteItems[iIndex].VoteItemName.Trim().Length > 0)
                        {
                            Query = "Insert into TableVoteItem(VoteItemId,[" + ActiveLanguage + "VoteItemName],TotalVote,IndexOrder,fk_Vote_Id) values (@VoteItemId,@VoteItemName,0,@IndexOrder,@fk_Vote_Id)";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();
                            sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@VoteItemId"].Value = VoteItems[iIndex].VoteItemId;

                            sqlCommand.Parameters.Add("@VoteItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@VoteItemName"].Value = VoteItems[iIndex].VoteItemName;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = VoteItems[iIndex].IndexOrder;

                            sqlCommand.Parameters.Add("@fk_Vote_Id", SqlDbType.VarChar);
                            sqlCommand.Parameters["@fk_Vote_Id"].Value = VoteId;
                            try
                            {
                                sqlCommand.ExecuteNonQuery();
                            }
                            catch { }

                            //xong roi lai update
                            Query = " Update TableVoteItem set [" + ActiveLanguage + "VoteItemName]=@VoteItemname,IndexOrder=@IndexOrder where VoteItemId=@VoteItemId";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();
                            sqlCommand.Parameters.Add("@VoteItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@VoteItemId"].Value = VoteItems[iIndex].VoteItemId;

                            sqlCommand.Parameters.Add("@VoteItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@VoteItemName"].Value = VoteItems[iIndex].VoteItemName;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = VoteItems[iIndex].IndexOrder;

                            sqlCommand.ExecuteNonQuery();


                            if (Portal.Environment.Environments.UseFrontEndDatabase)
                            {
                                ds.Tables[1].Rows.Add(new object[]{
                                       VoteItems[iIndex].VoteItemId,VoteItems[iIndex].VoteItemName,VoteItems[iIndex].IndexOrder.ToString()
                                   });
                            }
                        }
                    }
                }

                VoteIds += ")";
                if (VoteIds.Trim().Equals("()") == false)
                {
                    sqlCommand.Parameters.Clear();
                    sqlCommand.CommandText = "Delete TableVoteItem where not VoteItemId in " + VoteIds;
                    sqlCommand.ExecuteNonQuery();
                }

                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    string XmlData = ds.GetXml();
                //    Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //    for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //    {
                //        string RetValue = WServices[iIndexService].UpdateVotes(Environments.SecurityCode, XmlData);
                //        if (RetValue.Trim().Length > 0)
                //        {
                //            throw new Exception("Webservice:" + RetValue);
                //        }
                //    }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }



        public void DeleteVote(SqlTransaction sqlTransaction, string VoteId)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            Environment.Permission.DeletePermission(sqlTransaction, VoteId);
            string Query =
                    " Delete from TableVoteItem where fk_Vote_Id=@VoteId;" +
                    " Delete from TableVote where VoteId=@VoteId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@VoteId", SqlDbType.VarChar);
            sqlCommand.Parameters["@VoteId"].Value = VoteId;

            try
            {
                sqlCommand.ExecuteNonQuery();

                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    DataSet ds = new DataSet("xml");
                //    ds.Tables.Add("Vote");
                //    ds.Tables[0].Columns.Add("VoteId");
                //    ds.Tables[0].Rows.Add(new object[]{
                //                                           VoteId
                //                                      });
                //    string XmlData = ds.GetXml();
                //    Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //    for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //    {
                //        string RetValue = WServices[iIndexService].DeleteVote(Environments.SecurityCode, XmlData);
                //        if (RetValue.Trim().Length > 0)
                //        {
                //            throw new Exception("Webservice:" + RetValue);
                //        }
                //    }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void SyncWithServer(
             string ConnectionString,
             string VoteId,
             bool UseWsForDeploy,
             string ActiveLanguage,
             string PathRoot,
             string WorkingPath,
             string PortalAlias,
             string WsUrl,
             string WsSecurityCode)
        {
            if (UseWsForDeploy)
            {
                VoteCls oVoteCls = new VoteBLL().CreateModel(ConnectionString, ActiveLanguage, VoteId);
                DataSet ds = new DataSet();

                string Query =
                    " select * from TableVote where VoteId=@VoteId;" +
                    " select * from TableVoteItem where fk_Vote_Id=@VoteId";
                SqlConnection sqlConnectionCheck = new SqlConnection(ConnectionString);
                sqlConnectionCheck.Open();
                SqlCommand sqlCommandCheck = sqlConnectionCheck.CreateCommand();
                sqlCommandCheck.CommandText = Query;
                sqlCommandCheck.Parameters.Clear();
                sqlCommandCheck.Parameters.Add("@VoteId", SqlDbType.VarChar);
                sqlCommandCheck.Parameters["@VoteId"].Value = VoteId;

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommandCheck);
                sqlda.Fill(ds);

                ds.Tables[0].TableName = "TableVote";
                ds.Tables[1].TableName = "TableVoteItem";

                string XmlData = ds.GetXml();
                Framework.AppWs.WsReturnParamCls oWsReturnParamCls = Environments.CreateAppWs(WsUrl).SyncData(
                    WsSecurityCode,
                    VoteId,
                    oVoteCls.belongSite,
                    XmlData,
                    null,
                    null,
                    null,
                    Framework.AppWs.ESycDataType.Vote);
                if (oWsReturnParamCls.Error) throw new Exception("WS: " + oWsReturnParamCls.ErrorMsg);
            }
        }

        public void SyncWithServer(
            string VoteId)
        {
            string WsUrl = Environments.GetConfigFromXml("AppWs");
            string WsSecurityCode = Portal.Environment.Environments.WsSecurityCode;
            bool UseWsForDeploy = Environments.UseWsForDeploy;
            SyncWithServer(
                Environment.Environments.ConnectionString,
                VoteId,
                UseWsForDeploy,
                Environments.ActiveLanguage,
                Environments.PathRoot,
                Environments.WorkingPath,
                Portal.Environment.Environments.PortalAlias,
                WsUrl,
                WsSecurityCode);
        }
    }
}
